package mp3;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

/**
 * What is the purpose of introducing a database?
 * It is mainly to maintaining mp3 tags.
 * For instance, if for a album, the album artist is entered as DSP
 * But in the database all the DSPs are entered as Devi Sri Prasad
 * When the database is updated and maintain function is called.
 * All the existing music tags will be edited
 * Any folder that has music edit will be rezipped for backup
 * 
 * @author Mallampati
 *
 */
public class ManageDatabase {
	 // JDBC driver name and database URL
	   final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   final String DB_URL = "jdbc:mysql://localhost:3306/music";
	   final String TABLE = "telugu";
	   final String COMMA = ",";
	   final String QUOTE_COMMA = "\",\"";
	   

	   //  Database credentials
	   final String USER = "root";
	   final String PASS = "";
	   
	   private Connection conn = null;
	   private Statement stmt = null;
	   
	   public ManageDatabase() throws ClassNotFoundException {
		 //STEP 2: Register JDBC driver
		 Class.forName("com.mysql.jdbc.Driver");
	   }
	   
	   private void startConnection() throws SQLException {
	      //STEP 3: Open a connection
	      System.out.println("Connecting to database...");
	      conn = DriverManager.getConnection(DB_URL,USER,PASS);
	      System.out.println("Creating statement...");
	      stmt = conn.createStatement();
	   }
	   
	   private void endConnection() throws SQLException {
		    //close resources
			if(stmt!=null) {
				stmt.close();
			}
			if(conn!=null) {
				conn.close();
			}
	   }
	   
	   public void saveMP3(MP3Info mp3) throws SQLException {
		   startConnection();
		   String sql = "INSERT INTO " + TABLE + "(title, artist, album, track, year, album_artist, " +
		   		"composer, genre, record_label, track_total, lyricist, lyrics, rating) " +
                "VALUES (\""+mp3.getTitle()+QUOTE_COMMA+mp3.getArtist()+QUOTE_COMMA+mp3.getAlbum()+QUOTE_COMMA+mp3.getTrack()+QUOTE_COMMA+
                mp3.getYear()+QUOTE_COMMA+mp3.getAlbumArtist()+QUOTE_COMMA+mp3.getComposer()+QUOTE_COMMA+
                mp3.getGenre()+QUOTE_COMMA+mp3.getRecordLabel()+QUOTE_COMMA+mp3.getTrackTotal()+QUOTE_COMMA+
                mp3.getLyricist()+QUOTE_COMMA+mp3.getLyrics()+QUOTE_COMMA+mp3.getRating()+"\")";
		   try {
			   stmt.executeUpdate(sql);
		   } catch (MySQLIntegrityConstraintViolationException e) {
			   //ignore duplicate entries
		   }
		   endConnection();
	   }

	public MP3Info getMP3FromPrimaryKey(String title, String album, int year) throws SQLException {
		startConnection();
		MP3Info mp3 = null;
	      //STEP 4: Execute a query
	      String sql;
	      sql = "SELECT title, artist, album, track, year, album_artist, composer, genre," +
	      		"record_label, track_total, lyricist, lyrics, rating FROM " + TABLE
	      		+ " where title=\"" + title + "\" AND album=\"" + album + "\" AND year=" + year ;
	      ResultSet rs = stmt.executeQuery(sql);

	      //STEP 5: Extract data from result set
	      if(rs.next()){
	         //Retrieve by column name
	         String artist = rs.getString("artist");
	         int track = rs.getInt("track");
	         String albumArtist = rs.getString("album_artist");
	         String composer = rs.getString("composer");
	         String genre = rs.getString("genre");
	         String recordLabel = rs.getString("record_label");
	         int trackTotal = rs.getInt("track_total");
	         String lyricist = rs.getString("lyricist");
	         String lyrics = rs.getString("lyrics");
	         int rating = rs.getInt("rating");

	         //Display values
	         mp3 = new MP3Info(title, artist, album, track, year, albumArtist, composer, 
	        		 genre, recordLabel, trackTotal, lyricist, lyrics, rating);
	         System.out.println(mp3.toString());
	         
	      }
	      //STEP 6: Clean-up environment
	      rs.close();
	      endConnection();
	      return mp3;
		
	}
}
